package com.dp.appi.dao.impl;

import com.dp.appi.dao.ProjectDao;
import com.dp.appi.util.StringUtil;
import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by xiaoji on 2016/4/26.
 */
@Component
public class ProjectDaoImpl extends BaseDaoImpl implements ProjectDao {

    /**
     * 获取个人项目列表
     *
     * @param id 当前用户id
     * @param s  开始序列
     * @param n  获取条数
     * @return
     */
    @Override
    public List<Map<String, Object>> getSingleProject(String id, int s, int n, String project_name, Object org_id) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select");
        sb.append(" pb.project_id,");
        sb.append(" ps.stage_id,");
        sb.append(" ps.uuid,");//阶段uuid
        sb.append(" ps.manager,");
        sb.append(" ps.explorationer,");
        sb.append(" ps.designer,");
        sb.append(" ps.auditor,");
        sb.append(" pb.project_name,");
        sb.append(" ps.name as stage_name,");
        sb.append(" pb.lng,");
        sb.append(" pb.lat,");
        sb.append(" pb.thumb,");
        sb.append(" pb.project_type,");
        sb.append(" ps.stage,");
        sb.append(" ps.current_schedule schedule,");

//        sb.append(" if(ps.current_schedule=12&& ");
//        sb.append("find_in_set(?,explorationer)>0&&");
//        sb.append("find_in_set(?,manager)=0&&");
//        sb.append("find_in_set(?,designer)=0&&");
//        sb.append("find_in_set(?,auditor)=0");
//        sb.append(",1,0) is_exp,");


//        sb.append("  if(ps.current_schedule=11||(ps.current_schedule>11&& ");
        sb.append(" IF (ps.current_schedule > 11 && IFNULL(find_in_set(?, ps.manager),0) = 0 && IFNULL(find_in_set(?, ps.designer),0) = 0 && IFNULL(find_in_set(?, ps.auditor ),0) =0,");
        sb.append("  1, 0) is_exp,");


        sb.append(" IF(ps.finish_time is null,0,1) is_finish,");
        sb.append(" pb.codeset_version_id,");
        sb.append(" pb.cost_center,");
        sb.append(" UNIX_TIMESTAMP(ps.create_time) create_time");
        sb.append(" from p_stage ps");
        sb.append(" LEFT JOIN p_baseinfo pb on ps.project_id=pb.project_id");
        sb.append(" INNER JOIN t_s_user_org tsuo on tsuo.org_id=pb.cost_center");
        sb.append(" where pb.cost_center = ? and (ps.manager like ? or ps.explorationer like ?");
        sb.append(" or ps.designer like ? or ps.auditor like ? ) and pb.deleted=0 and ps.deleted=0 AND tsuo.user_id=?");
        //加上项目归属条件查询

        if (!StringUtil.isEmpty(project_name)) {
            sb.append(" and (ps.name like '%" + project_name + "%' or pb.project_name like '%" + project_name + "%') ");
        }

        sb.append(" order by ps.create_time desc limit ?,?");
        String str = "%" + id + "%";
//        return getJdbcTemplate().queryForList(sb.toString(),new Object[]{
//                id,id,id,id,str,str,str,str,s,n
//        });
        return getJdbcTemplate().queryForList(sb.toString(), new Object[]{
                id, id, id, org_id, str, str, str, str, id, s, n
        });
    }

    /**
     * 条件查询个人项目
     *
     * @param id    当前用户id
     * @param s     开始序列
     * @param n     获取条数
     * @param param 查询参数
     * @return
     */
    @Override
    public List<Map<String, Object>> getProjectByParam(String id, int s, int n, Map<String, String> param, Object org_id) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select");
        sb.append(" pb.project_id,");
        sb.append(" ps.stage_id,");
        sb.append(" ps.uuid,");
        sb.append(" ps.manager,");
        sb.append(" ps.explorationer,");
        sb.append(" ps.designer,");
        sb.append(" ps.auditor,");
        sb.append(" pb.project_name,");
        sb.append(" ps.name as stage_name,");
        sb.append(" pb.thumb,");
        sb.append(" pb.lng,");
        sb.append(" pb.lat,");
        sb.append(" pb.project_type,");
        sb.append(" ps.stage,");
        sb.append(" ps.current_schedule schedule,");
        sb.append(" ps.design_unit,");
        sb.append(" ps.owner_unit,");


//        sb.append(" if(ps.explorationer like ?,1,0) as is_exp,");

//        sb.append(" if(ps.current_schedule=12&& ");
//        sb.append("find_in_set(?,explorationer)>0&&");
//        sb.append("find_in_set(?,manager)=0&&");
//        sb.append("find_in_set(?,designer)=0&&");
//        sb.append("find_in_set(?,auditor)=0");
//        sb.append(",1,0) is_exp,");
        sb.append(" IF (ps.current_schedule > 11 && IFNULL(find_in_set(?, ps.explorationer),0) > 0&& IFNULL(find_in_set(?, ps.manager),0) = 0 && IFNULL(find_in_set(?, ps.designer),0) = 0 && IFNULL(find_in_set(?, ps.auditor ),0) =0,");
        sb.append("  1, 0) is_exp,");


        sb.append(" IF(ps.finish_time is null,0,1) is_finish,");
        sb.append(" pb.codeset_version_id,");
        sb.append(" pb.cost_center,");
        sb.append(" UNIX_TIMESTAMP(ps.create_time) create_time");
        sb.append(" from p_stage ps");
        sb.append(" LEFT JOIN p_baseinfo pb on ps.project_id=pb.project_id");
        sb.append(" INNER JOIN t_s_user_org tsuo on tsuo.org_id=pb.cost_center");
        sb.append(" where pb.cost_center = ? and (ps.manager like ? or ps.explorationer like ?");
        sb.append(" or ps.designer like ? or ps.auditor like ? ) and pb.deleted=0 and ps.deleted=0  AND tsuo.user_id=?");
        //项目名称
        if (!StringUtils.isEmpty(param.get("project_name"))) {
            String project_name = String.valueOf(param.get("project_name")).replace(" ", "%");
            sb.append(" and pb.project_name like '%" + project_name + "%'");
        }
        //开始年份
        if (!StringUtils.isEmpty(param.get("start_year"))) {
            String start_year = param.get("start_year");
            sb.append(" and date_format(ps.finish_time,'%Y') >= " + start_year);
        }
        //结束年份
        if (!StringUtils.isEmpty(param.get("end_year"))) {
            String end_year = param.get("end_year");
            sb.append(" and date_format(ps.finish_time,'%Y') <= " + end_year);
        }
        //项目阶段
        if (!StringUtils.isEmpty(param.get("current_stage"))) {
            Integer current_stage = Integer.valueOf(param.get("current_stage"));
            sb.append(" and pb.current_stage = " + current_stage);
        }
        //项目类型
        if (!StringUtils.isEmpty(param.get("project_type"))) {
            Integer project_type = Integer.valueOf(param.get("project_type"));
            sb.append(" and pb.project_type = " + project_type);
        }
        //项目进度
        if (!StringUtils.isEmpty(param.get("current_schedule"))) {
            Integer current_schedule = Integer.valueOf(param.get("current_schedule"));
            sb.append(" and pb.current_schedule = " + current_schedule);
        }
        //业主单位
        if (!StringUtils.isEmpty(param.get("owner_unit"))) {
            String owner_unit = param.get("owner_unit");
            sb.append(" and ps.owner_unit = '" + owner_unit + "'");
        }
        //设计单位
        if (!StringUtils.isEmpty(param.get("design_unit"))) {
            String design_unit = param.get("design_unit");
            sb.append(" and ps.design_unit = '" + design_unit + "'");
        }
        sb.append(" order by ps.create_time desc limit ?,?");
        String str = "%" + id + "%";
        return getJdbcTemplate().queryForList(sb.toString(), new Object[]{
                id, id, id, id, org_id, str, str, str, str, id, s, n
        });
    }

    /**
     * 创建项目基本信息
     *
     * @param param
     */
    @Override
    public Long createPBaseinfo(final Map<String, String> param, final String id) {
        final String sql = "INSERT INTO p_baseinfo(project_name,address,lat,lng,alt,weather_zone,power_zone,linkphone,linkman," +
                "voice_remark,characters_remark,codeset_version_id,current_stage,current_schedule," +
                "creater,is_finish,create_time,deleted,project_type,cost_center)" +
                " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW(),?,?,?)";
        KeyHolder key = new GeneratedKeyHolder();
        getJdbcTemplate().update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, param.get("project_name"));
                ps.setString(2, param.get("address"));
                ps.setBigDecimal(3, new BigDecimal(param.get("lat")));
                ps.setBigDecimal(4, new BigDecimal(param.get("lng")));

                BigDecimal alt = new BigDecimal(param.get("alt"));
                if (alt.intValue() == 0)
                    ps.setBigDecimal(5, null);
                else
                    ps.setBigDecimal(5, alt);


                if (param.get("weather_zone") == null) ps.setNull(6, Types.INTEGER);
                else ps.setInt(6, Integer.parseInt(param.get("weather_zone")));
                if (param.get("power_zone") == null) ps.setNull(7, Types.INTEGER);
                else ps.setInt(7, Integer.parseInt(param.get("power_zone")));
                if (param.get("linkphone") == null) ps.setNull(8, Types.VARCHAR);
                else ps.setString(8, param.get("linkphone"));
                if (param.get("linkman") == null) ps.setNull(9, Types.VARCHAR);
                else ps.setString(9, param.get("linkman"));
                if (param.get("voice_remark") == null) ps.setNull(10, Types.VARCHAR);
                else ps.setString(10, param.get("voice_remark"));
                if (param.get("characters_remark") == null) ps.setNull(11, Types.VARCHAR);
                else ps.setString(11, param.get("characters_remark"));
                ps.setNull(12,Types.BIGINT);
                ps.setInt(13, Integer.parseInt("14"));
                ps.setInt(14, 12);
                ps.setString(15, id);
                ps.setInt(16, 0);
                ps.setInt(17, 0);
                if (param.get("project_type") == null) ps.setNull(18, Types.INTEGER);
                else ps.setInt(18, Integer.parseInt(param.get("project_type")));
                ps.setString(19, String.valueOf(param.get("cost_center")));
                return ps;
            }
        }, key);
        long bid = key.getKey().longValue();
        return bid;
    }

    /**
     * 创建项目阶段
     *
     * @param param
     */
    @Override
    public Long createPStage(final Map<String, String> param, final Long project_id) {
        final String sql = "INSERT INTO p_stage(project_id,stage,manager,explorationer," +
                "create_time,design_unit,name,designer,auditor) " +
                " VALUES (?,?,?,?,NOW(),?,?,?,?)";
        KeyHolder key = new GeneratedKeyHolder();
        getJdbcTemplate().update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setLong(1, project_id);
                ps.setInt(2, Integer.parseInt("14"));
                if (param.get("manager") == null) ps.setNull(3, Types.VARCHAR);
                else ps.setString(3, param.get("manager"));
                if (param.get("manager") == null) ps.setNull(7, Types.VARCHAR);
                else ps.setString(7, param.get("manager"));
                if (param.get("manager") == null) ps.setNull(8, Types.VARCHAR);
                else ps.setString(8, param.get("manager"));
                if (param.get("explorationer") == null) ps.setNull(4, Types.VARCHAR);
                else ps.setString(4, param.get("explorationer"));
                ps.setString(5, param.get("design_unit"));
                ps.setString(6, param.get("project_name"));
                return ps;
            }
        }, key);
        long id = key.getKey().longValue();
        return id;
    }

    /**
     * 创建土质信息表
     *
     * @param param
     */
    @Override
    public Long createPSoil(final Map<String, String> param, final Long project_id) {
        final String sql = "INSERT INTO p_soil(project_id,soil_type,soil_value) VALUES (?,?,?)";
        KeyHolder key = new GeneratedKeyHolder();
        getJdbcTemplate().update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setLong(1, project_id);
                ps.setInt(2, Integer.parseInt(param.get("soil_type")));
                ps.setString(3, param.get("soil_value"));
                return ps;
            }
        }, key);
        long id = key.getKey().longValue();
        return id;
    }

    /**
     * 创建二次转运表
     *
     * @param param
     */
    @Override
    public Long createPSecondTransport(final Map<String, String> param, final Long project_id) {
        final String sql = "INSERT INTO p_second_transport(project_id,transport_type,distance) VALUES (?,?,?)";
        KeyHolder key = new GeneratedKeyHolder();
        getJdbcTemplate().update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setLong(1, project_id);
                ps.setInt(2, Integer.parseInt(param.get("transport_type")));
                ps.setBigDecimal(3, new BigDecimal(param.get("distance")));
                return ps;
            }
        }, key);
        long id = key.getKey().longValue();
        return id;
    }

    /**
     * 创建项目进度 初始化第一个进度
     *
     * @param param
     */
    @Override
    public Long createPSchedule(Map<String, String> param, final Long project_id, final Long stage_id) {
        final String sql = "INSERT INTO p_schedule(project_id,stage_id,project_schedule,create_time) " +
                " VALUES(?,?,?,NOW()) ";
        KeyHolder key = new GeneratedKeyHolder();
        getJdbcTemplate().update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setLong(1, project_id);
                ps.setLong(2, stage_id);
                ps.setInt(3, 12);
                return ps;
            }
        }, key);
        long id = key.getKey().longValue();
        return id;
    }

    /**
     * 删除项目阶段
     *
     * @param stage_id 阶段id
     * @param id       当前用户id
     */
    @Override
    public void delProjectStage(Long stage_id, String id) {
        String sql = "update p_stage set deleted=1 where stage_id=? and " +
                "(manager like ? or explorationer like ? or designer like ? or auditor like ?)";
        String str = "%" + id + "%";
        getJdbcTemplate().update(sql, new Object[]{stage_id, str, str, str, str});
    }

    /**
     * 查询项目阶段信息
     *
     * @param stage_id 项目阶段信息
     * @return
     */
    @Override
    public Map<String, Object> projectInfo(Long stage_id) {
        StringBuffer sql = new StringBuffer();
        sql.append(" select ps.project_id,ps.stage_id,pb.project_name,pb.project_type,pb.project_attribute,pb.region,pb.cost_center, ");
        sql.append(" pb.address,pb.detail_address,pb.lng,pb.lat,pb.alt,pb.linkphone,pb.linkman,pb.voice_remark,pb.characters_remark, ");
        sql.append("  pb.weather_zone,pb.power_zone,pb.wire_type,pb.codeset_version_id,ps.stage,ps.manager manager_id,ps.explorationer explorationer_id, ");
        sql.append(" ps.designer designer_id,ps.auditor auditor_id,ps.name as stage_name,ps.design_unit design_unit_id, ");
        sql.append(" ps.owner_unit owner_unit_id,sd1.departname design_unit,sd2.departname owner_unit ,CONCAT(tst.typename,ccv.codeset_version) codeset_version");
        sql.append(" from p_stage ps ");
        sql.append(" LEFT JOIN p_baseinfo pb on ps.project_id=pb.project_id ");
        sql.append(" LEFT JOIN t_s_depart sd1 on ps.design_unit = sd1.ID ");
        sql.append(" LEFT JOIN t_s_depart sd2 on ps.owner_unit = sd2.ID ");
        sql.append(" LEFT JOIN c_codeset_version ccv  on pb.codeset_version_id = ccv.codeset_version_id");
        sql.append(" LEFT JOIN t_s_type tst on tst.typecode = ccv.codeset_type and tst.typegroupid = 'sds807081511112c501521x1ebc2rr04'");
        sql.append(" where ps.stage_id=? and ps.deleted=0 ");
        Map<String, Object> result = findOneObject(sql.toString(), new Object[]{stage_id});
        if (result == null) {
            result = new HashMap<>();
        }

        //人员转义 manager explorationer designer auditor
        result.put("manager", escapeUser(result.get("manager_id"), ","));
        result.put("explorationer", escapeUser(result.get("explorationer_id"), ","));
        result.put("designer", escapeUser(result.get("designer_id"), ","));
        result.put("auditor", escapeUser(result.get("auditor_id"), ","));

        //土壤信息
        String sql2 = "select pso.soil_type,pso.soil_value from p_stage ps" +
                " LEFT JOIN p_soil pso on pso.project_id=ps.project_id where ps.stage_id=? and pso.soil_value!=0";
        List<Map<String, Object>> list2 = getJdbcTemplate().queryForList(sql2, new Object[]{stage_id});
        result.put("soil", list2);
        //二次转运
        String sql3 = "select pst.transport_type,pst.distance from p_stage ps" +
                " LEFT JOIN p_second_transport pst on pst.project_id=ps.project_id where ps.stage_id=?";
        List<Map<String, Object>> list3 = getJdbcTemplate().queryForList(sql3, new Object[]{stage_id});
        result.put("transport", list3);
        return result;
    }

    /**
     * 修改项目基本信息
     *
     * @param param 参数
     * @param id    阶段id
     */
    @Override
    public void upProject(Map<String, String> param, String id) {
        String sql = "update p_baseinfo set project_name=?,project_type=?,project_attribute=?," +
                "region=?,address=?,detail_address=?,lng=?,lat=?,alt=?,linkphone=?,linkman=?," +
                "voice_remark=?,characters_remark=?,weather_zone=?,power_zone=?,wire_type=?," +
                "codeset_version_id=? where project_id=?";
        getJdbcTemplate().update(sql, new Object[]{

                param.get("project_name"),
                Integer.parseInt(param.get("project_type")),
                Integer.parseInt(param.get("project_attribute")),
                Integer.parseInt(param.get("region")),
                param.get("address"),
                param.get("detail_address"),
                new BigDecimal(param.get("lng")),
                new BigDecimal(param.get("lat")),
                new BigDecimal(param.get("alt")),
                param.get("linkphone"),
                param.get("linkman"),
                param.get("voice_remark"),
                param.get("characters_remark"),
                Integer.parseInt(param.get("weather_zone")),
                Integer.parseInt(param.get("power_zone")),
                Integer.parseInt(param.get("wire_type")),
                Long.parseLong(param.get("codeset_version_id")),
                Long.parseLong(param.get("project_id"))
        });
    }

    /**
     * 修改项目版本
     * @param codeset_version_id
     * @param project_id
     */
    @Override
    public void upProjectVersionid(long codeset_version_id , long  project_id) {
        String sql = "update p_baseinfo set " +
                "codeset_version_id=? where project_id=?";
        getJdbcTemplate().update(sql, new Object[]{
                codeset_version_id,project_id
        });
    }
        /**
         * 修改项目阶段基本信息
         *
         * @param param 参数
         * @param id    用户id
         */
    @Override
    public void upProjectStage(Map<String, String> param, String id) {
        String sql = "update p_stage set manager=?,explorationer=?,designer=?,auditor=?,name=?" +
                " where stage_id=? and project_id=?";
        getJdbcTemplate().update(sql, new Object[]{
                param.get("manager"), param.get("explorationer"), param.get("designer"), param.get("auditor"),
                param.get("project_name"),
                param.get("stage_id"), param.get("project_id")
        });
    }

    /**
     * 修改项目土壤基本信息
     *
     * @param soilJson   参数
     * @param project_id 项目id
     */
    @Override
    public void upProjectSoil(String soilJson, String project_id) {
        List<Map<String, Object>> list = new Gson().fromJson(soilJson, new TypeToken<List<Map<String, Object>>>() {
        }.getType());
        //批量删除
        String sql1 = "delete from p_soil where project_id=?";
        getJdbcTemplate().update(sql1, new Object[]{Long.parseLong(project_id)});
        //批量插入
        if (list.size() > 0) {
            List<Object[]> listParam = new ArrayList<>();
            for (int i = 0; i < list.size(); i++) {
                Map<String, Object> temp = list.get(i);
                Object[] param2 = new Object[]{
                        Long.parseLong(project_id),
                        (int) (Double.parseDouble(String.valueOf(temp.get("soil_type")))),
                        new BigDecimal(String.valueOf(temp.get("soil_value")))
                };
                listParam.add(param2);
            }
            String sql2 = "insert into p_soil(project_id,soil_type,soil_value) values(?,?,?)";
            getJdbcTemplate().batchUpdate(sql2, listParam);
        }
    }

    /**
     * 修改项目二次转运基本信息
     *
     * @param transportJson 参数
     * @param project_id    项目id
     */
    @Override
    public void upProjectSecondTransport(String transportJson, String project_id) {
        List<Map<String, Object>> list = new Gson().fromJson(transportJson, new TypeToken<List<Map<String, Object>>>() {
        }.getType());
        //批量删除
        String sql1 = "delete from p_second_transport where project_id=?";
        getJdbcTemplate().update(sql1, new Object[]{Long.parseLong(project_id)});
        //批量插入
        if (list.size() > 0) {
            List<Object[]> listParam = new ArrayList<>();
            for (int i = 0; i < list.size(); i++) {
                Map<String, Object> temp = list.get(i);
                Object[] param2 = new Object[]{
                        Long.parseLong(String.valueOf(project_id)),
                        (int) (Double.parseDouble(String.valueOf(temp.get("transport_type")))),
                        new BigDecimal(String.valueOf(temp.get("distance")))
                };
                listParam.add(param2);
            }
            String sql2 = "insert into p_second_transport(project_id,transport_type,distance) values(?,?,?)";
            getJdbcTemplate().batchUpdate(sql2, listParam);
        }
    }

    /**
     * 获取数据权限类别
     *
     * @param id
     * @return
     */
    @Override
    public Map<String, Object> rabcClassify(String id, long stage_id) {
        StringBuffer sql = new StringBuffer();
        sql.append(" select if(stage=11,1,if(stage=12&&(find_in_set(?,manager)>0|| ");
        sql.append(" find_in_set(?,designer)>0|| ");
        sql.append(" find_in_set(?,auditor)>0),2,1)) classify from p_stage a ");
        sql.append(" where (a.explorationer like ? ");
        sql.append(" or  a.manager like ? ");
        sql.append(" or  a.auditor like ? ");
        sql.append(" or  a.designer like ?) ");
        sql.append(" and a.deleted = 0 ");
        sql.append(" and stage_id = ? ");
        String idl = "%" + id + "%";
        return queryForMap(sql.toString(), id, id, id, idl, idl, idl, idl, stage_id);
    }

    /**
     * 条件查询个人所有项目
     *
     * @param id
     * @param param
     * @return
     */
    @Override
    public List<Map<String, Object>> getAllProjectByParam(String id, Map<String, String> param, Object org_id) {
        List paramL = new ArrayList();
        paramL.add(id);
        paramL.add(id);
        paramL.add(id);
        paramL.add(id);
        paramL.add(org_id);
        paramL.add(id);
        paramL.add("%" + id + "%");
        paramL.add("%" + id + "%");
        paramL.add("%" + id + "%");
        paramL.add("%" + id + "%");
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT b.project_id,b.project_name,b.lng,b.lat,count(1) num,if(c.finish_time is null,0,1) is_finish, ");
        sql.append("b.thumb,b.project_type,b.codeset_version_id,b.cost_center,");
        sql.append(" a.manager,");
        sql.append(" a.explorationer,");
        sql.append(" a.designer,");
        sql.append(" a.auditor,");

        sql.append("GROUP_CONCAT(a.stage) stage,");
        sql.append("GROUP_CONCAT(a.stage_id) stage_ids, ");
        sql.append("GROUP_CONCAT(a.current_schedule) schedule, ");
        sql.append("GROUP_CONCAT(UNIX_TIMESTAMP(a.create_time)) create_time, ");
        sql.append("GROUP_CONCAT(if(a.current_schedule>=12&&");
        sql.append("find_in_set(?,a.explorationer)>0&&");
        sql.append("find_in_set(?,a.manager)=0&&");
        sql.append("find_in_set(?,a.designer)=0&&");
        sql.append("find_in_set(?,a.auditor)=0");
        sql.append(",1,0)) is_exp ");

        sql.append("  FROM p_stage a ");
        sql.append(" INNER JOIN p_baseinfo b ON a.project_id = b.project_id ");
        sql.append(" INNER JOIN t_s_user_org tsuo on tsuo.org_id=b.cost_center");
        sql.append(" LEFT JOIN p_stage c ON b.current_stage = c.stage and b.project_id = c.project_id ");
        sql.append(" WHERE b.cost_center = ? and a.deleted = 0 ");
        sql.append(" AND b.deleted = 0 ");
        sql.append(" AND c.deleted = 0 ");
        sql.append(" AND tsuo.user_id = ? ");
        sql.append(" AND ( ");
        sql.append("  a.explorationer LIKE ? ");
        sql.append(" OR a.designer LIKE ? ");
        sql.append(" OR a.auditor LIKE ? ");
        sql.append("  OR a.manager LIKE ?) ");
        if (null != param.get("project_name")) {
            paramL.add("%" + param.get("project_name") + "%");
            sql.append(" AND project_name LIKE ? ");
        }
        sql.append(" GROUP BY b.project_id ");
        return getJdbcTemplate().queryForList(sql.toString(), paramL.toArray());
    }

    /**
     * 查询阶段的基本信息
     *
     * @param stage_id 项目阶段信息
     * @return
     */
    @Override
    public Map<String, Object> projectStageInfo(Long stage_id) {
        StringBuffer sql = new StringBuffer();
        sql.append(" select * from p_stage a ");
        sql.append(" LEFT JOIN p_baseinfo b on a.project_id=b.project_id ");
        sql.append("  where a.stage_id= ? ");
        Map<String, Object> result = queryForMap(sql.toString(), stage_id);
        return result;
    }

    /**
     * 获取最近一次的定位点踏勘数据
     *
     * @param id
     * @param stage_id
     * @param classify
     * @return
     */
    @Override
    public Map<String, Object> getLastExp(Object id, Long stage_id, int classify, Integer pt_type) {
        StringBuffer sql = new StringBuffer();
        sql.append("select a.* from p_exploration a ");
        sql.append("LEFT JOIN p_pt b on a.pt_id = b.pt_id ");
        sql.append("where pt_type = ? and stage_id = ? ");
        sql.append("and classify = ? and exploration_person = ? ");
        sql.append("ORDER BY exploration_id desc");
        List<Map<String, Object>> result = getJdbcTemplate().queryForList(sql.toString(), pt_type, stage_id, classify, id);
        if (null != result && result.size() > 0) {
            return result.get(0);
        }
        return null;
    }

}
